package love.yuanmengrobot.als.business.dao.impl;
/*
 *   @Studio:CHJY · 源梦科技
 *   @Author:COOLIGHT
 *   @Ide:IntelliJ IDEA
 *   @Creation time:2021/6/27 0027 16:54
 */

import love.yuanmengrobot.als.business.dao.SearchScoreInfoDao;
import love.yuanmengrobot.als.business.javaBean.ScoreView;
import love.yuanmengrobot.als.utilPublic.JdbcUtil;
import love.yuanmengrobot.als.utilPublic.SqlString;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class SearchScoreInfoDaoImpl implements SearchScoreInfoDao {
    private static final int perPage = 20;

    /**
     * 获取学生成绩结果集
     *
     * @param id   学号
     * @param name 姓名
     * @param page 当前页码
     * @return 当前页的学生成绩结果
     */
    @Override
    public List<ScoreView> searchScoreInfo(String id, String name, int page) {
        List<ScoreView> list = new ArrayList<>();
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtil.getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(SqlString.selectScoreByStuNoOrStuName(id, name, page, perPage));//查询已修课程情况
            //已修课程结果处理
            while (rs.next()) {
                ScoreView view = new ScoreView();
                view.setScore_id(rs.getString("score_id"));
                view.setRowNum(rs.getInt("rn"));
                view.setCourse_term(rs.getString("course_term"));
                view.setStu_no(rs.getString("stu_no"));
                view.setStu_name(rs.getString("stu_name"));
                view.setMajor_name(rs.getString("major_name"));
                view.setGrade(rs.getInt("grade"));
                view.setClazz(rs.getString("clazz"));
                view.setCourse_code(rs.getString("course_code"));
                view.setCourse_id(rs.getString("course_id"));
                view.setCourse_name(rs.getString("course_name"));
                view.setScore(rs.getString("score"));
                view.setCredit(rs.getDouble("credit"));
                view.setTest_type(rs.getString("test_type"));
                view.setScore_type(rs.getString("score_type"));
                view.setCourse_type(rs.getString("course_type"));
                list.add(view);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {//回收资源
            JdbcUtil.close(rs, stmt, conn);
        }
        return list;
    }

    /**
     * 获取学生成绩总共有多少页
     *
     * @param id   学号
     * @param name 姓名
     * @return 总页数
     */
    @Override
    public int getAllPage(String id, String name) {
        int page = 0;
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtil.getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(SqlString.selectScorePage(id, name));//查询已修课程情况
            rs.next();
            page = rs.getInt(1);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {//回收资源
            JdbcUtil.close(rs, stmt, conn);
        }
        return page / (perPage + 1) + 1;
    }

    /**
     * 删除学生成绩
     *
     * @param id 成绩ID
     */
    @Override
    public void deleteScore(String id) {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = JdbcUtil.getConnection();
            stmt = conn.prepareStatement(SqlString.deleteScoreById(id));
            stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {//回收资源
            JdbcUtil.close(stmt, conn);
        }
    }

    @Override
    public void updateScore(String id, String score) {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = JdbcUtil.getConnection();
            stmt = conn.prepareStatement(SqlString.updateScore(id,score));
            stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {//回收资源
            JdbcUtil.close(stmt, conn);
        }
    }
}
